/*
 * 文件名：ExcelUtils
 * 版权：Copyright @ 2017 excel All Rights Reserved.
 * 描述：
 * 修改人：lemon
 * 修改时间：2017年9月24日 下午12:33:34
 * 修改内容：〈修改内容〉
 */
package com.ctcemti.mdm.common.util;

import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;

import java.text.DecimalFormat;
import java.util.regex.Pattern;

/* *
 * @Author bxf
 * @Description excel工具获取值类
 * @Date 9:01 2019/9/13
 * @param null
 * @return
 */
public class ExcelUtils {

	/**
	 * excel 文件扩展名
	 */
	private final static String EXCEL_FILE = ".xls";
	/**
	 * 文件编码格式-UTF-8
	 */
	private final static String UTF_FILE_CODE = "UTF-8";
	/**
	 * 响应头-名字
	 */
	private final static String RESPONSE_HEADER_NAME = "Content-Disposition";
	/**
	 * 响应头-值
	 */
	private final static String RESPONSE_HEADER_VALUE = "attachment;filename=";
	/**
	 * 响应文本类型
	 */
	private final static String RESPONSE_CONTENT_TYPE = "application/octet-stream;charset=";
	/**
	 * 2003 版本的excel
	 */
	private final static String Excel_2003 = ".xls";
	/**
	 * 2007 版本的excel
	 */
	private final static String Excel_2007 = ".xlsx";

	private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0.00000");// 格式化 number为整

	private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00000%");//格式化分比格式，后面不足2位的用0补齐

//	private static final DecimalFormat df_per_ = new DecimalFormat("0.00%");//格式化分比格式，后面不足2位的用0补齐,比如0.00,%0.01%

//	private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // 格式化日期字符串

	private static final FastDateFormat FAST_DATE_FORMAT = FastDateFormat.getInstance("yyyy-MM-dd hh:mm:ss");

	private static final DecimalFormat DECIMAL_FORMAT_NUMBER  = new DecimalFormat("0.00E000"); //格式化科学计数器

	private static final Pattern POINTS_PATTERN = Pattern.compile("0.0+_*[^/s]+"); //小数匹配

	public static FormulaEvaluator evaluator;

	public static void setEvaluator(FormulaEvaluator formulaEvaluator){
		evaluator = formulaEvaluator;
	}


	/* *
	 * @Author bxf
	 * @Description 分多种格式解析单元格的值
	 * @Date 8:59 2019/9/13
	 * @param cell
	 * @return java.lang.Object
	 */
	public static Object getNormalValue(Cell cell) {
		Object value = null;
		switch (cell.getCellTypeEnum()) {
			case _NONE:
				break;
			case STRING:
				value = cell.getStringCellValue();
				break;
			case NUMERIC:
				if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)){ //日期
					value = FAST_DATE_FORMAT.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue()));//统一转成 yyyy/MM/dd
				} else if("@".equals(cell.getCellStyle().getDataFormatString())
						|| "General".equals(cell.getCellStyle().getDataFormatString())
						|| "0_ ".equals(cell.getCellStyle().getDataFormatString())){
					//文本  or 常规 or 整型数值
					value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
				} else if(POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型
					value = cell.getNumericCellValue();  //直接显示
				} else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数
					value = cell.getNumericCellValue();	//待完善
					value = DECIMAL_FORMAT_NUMBER.format(value);
				} else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比
					value = cell.getNumericCellValue(); //待完善
					value = DECIMAL_FORMAT_PERCENT.format(value);
				} else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数
					value = cell.getNumericCellValue(); ////待完善
				} else { //货币
					value = cell.getNumericCellValue();
					value = DecimalFormat.getCurrencyInstance().format(value);
				}
				break;
			case BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			case FORMULA://为公式
				value = getCellValue(evaluator.evaluate(cell));
				break;
			case BLANK:
				value = "";
				break;
			default:
				value = "";
		}
		return value;
	}

	private static String getCellValue(CellValue cell) {
		String cellValue = null;
		switch (cell.getCellTypeEnum()) {
			case STRING:
				cellValue=cell.getStringValue();
				break;
			case NUMERIC:
				cellValue=String.valueOf(cell.getNumberValue());
				break;
			case FORMULA:
				break;
			default:
				break;
		}
		return cellValue;
	}


}
